import json
import datetime
import time
import os

from flask import Flask, request, Response, render_template, flash, send_from_directory, redirect, url_for
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import or_
from pip._vendor import requests
from model import RecordForm, Build, AddFood, FoodDemand
from openpyxl import Workbook

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "sqlite:///identifier.sqlite"
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = 'Hello World'
app.config['APPID'] = 'wx1ab6e8ad8006a07d'
app.config['password'] = '114514'
app.config['wc'] = 0
app.config['wa'] = 1
app.config['sh'] = 2
app.config['fan'] = 3
app.config['cai'] = 4

cn_config = {'record_id': '记录序号',
             'user_id': '用户序号',
             'place_id': '设施序号',
             'created': '时间',
             'opt': '操作',
             'student_number': '学生号',
             'student_name': '姓名',
             'dormitory': '宿舍楼',
             'floor': '楼层',
             'place': '设施',
             'room_number': '寝室',
             'sh': '洗澡',
             'wa': '洗漱',
             'wc': '厕所',
             '1': '进入',
             '-1': '离开',
             'fan': '饭',
             'cai': '菜'
             }

db = SQLAlchemy(app)


def get_time(timestamp):
    time_local = time.localtime(timestamp)
    ret = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
    return ret


# class MyModel(db.Model):
#     def to_dict(self):
#         return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class User(db.Model):
    __tablename__ = 'User'
    user_id = db.Column(db.Integer, primary_key=True)
    wx_id = db.Column(db.String)
    wx_name = db.Column(db.String)
    student_number = db.Column(db.String)
    student_name = db.Column(db.String)
    dormitory_id = db.Column(db.Integer)
    room_number = db.Column(db.Integer)
    floor = db.Column(db.Integer)
    status = db.Column(db.Integer)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Dormitory(db.Model):
    __tablename__ = 'Dormitory'
    dormitory_id = db.Column(db.Integer, primary_key=True)
    dormitory = db.Column(db.String, nullable=False)
    floor = db.Column(db.Integer, nullable=False)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Place(db.Model):
    __tablename__ = 'Place'
    place_id = db.Column(db.Integer, primary_key=True)
    dormitory_id = db.Column(db.Integer)
    floor = db.Column(db.Integer)
    occupied = db.Column(db.Integer)
    place = db.Column(db.String)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


class Record(db.Model):
    __tablename__ = 'Record'
    record_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer)
    place_id = db.Column(db.String)
    created = db.Column(db.TIMESTAMP, default=time.time())
    opt = db.Column(db.String)
    student_number = db.Column(db.String)
    student_name = db.Column(db.String)
    dormitory = db.Column(db.String)
    floor = db.Column(db.Integer)
    place = db.Column(db.String)
    room_number = db.Column(db.String)

    def to_dict(self):
        ret = {c.name: getattr(self, c.name) for c in self.__table__.columns}
        ret['created'] = get_time(ret['created'])
        return ret


class P2P(db.Model):
    __tablename__ = 'P2P'
    P2P_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, nullable=False)
    dormitory_id = db.Column(db.Integer, nullable=False)
    floor = db.Column(db.Integer, nullable=False)
    Description = db.Column(db.String, nullable=False)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}


@app.route('/w_login', methods=['GET', 'POST'])
def w_login():
    code = request.json['code']
    wx_name = request.json['name']
    req = "https://api.weixin.qq.com/sns/jscode2session?appid=%s&secret=%s&js_code=%s&grant_type=authorization_code" % (
        "wx1ab6e8ad8006a07d", "06570c608e0f4e234fd62c9e653f596f", code)
    res = requests.get(req, verify=False)
    openid = res.json()['openid']
    user = User.query.filter_by(wx_id=openid).first()
    if not user:
        new_user = User(wx_id=openid, wx_name=wx_name, dormitory_id=1, floor=0, status=0, student_name='未设定',
                        student_number='未设定', room_number='未设定')
        db.session.add(new_user)
        db.session.commit()
    user = User.query.filter_by(wx_id=openid).first()
    ret = user.to_dict()
    ret['dormitory'] = Dormitory.query.get(user.dormitory_id).dormitory
    ret['wx_id'] = openid
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/select_dormitory', methods=['GET', 'POST'])
def select_dormitory():
    wx_id = request.json['wx_id']
    dormitory_id = request.json['dormitory_id']
    floor = request.json['floor']
    room_number = request.json['room_number']
    student_name = request.json['student_name']
    ret = {'status': 'Success'}
    user = User.query.filter_by(wx_id=wx_id).first()
    status = user.status
    places = Place.query.filter_by(dormitory_id=dormitory_id).filter_by(floor=user.floor).all()
    for place in places:
        if app.config[place.place] <= 2 and status & (1 << app.config[place.place]):
            Place.query.filter_by(place_id=place.place_id).update({'occupied': place.occupied - 1})
    User.query.filter_by(wx_id=wx_id).update(
        {'dormitory_id': dormitory_id, 'floor': floor, 'status': 0, 'student_name': student_name,
         'room_number': room_number})
    db.session.commit()
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/profile', methods=['GET', 'POST'])
def profile():
    wx_id = request.json['wx_id']
    user = User.query.filter_by(wx_id=wx_id).first()
    ret = {'student_number': user.student_number, 'student_name': user.student_name, 'dormitory': user.dormitory,
           'floor': user.floor}
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/list_dormitory', methods=['GET', 'POST'])
def list_dormitory():
    ret = {'columns': [], 'columnData': []}
    dormitories = Dormitory.query.filter(Dormitory.dormitory_id != 1).all()
    fir_first = []
    sec_first = []
    for item in dormitories:
        fir_first.append({'label': item.dormitory, 'id': item.dormitory_id})
        floors = [j + 1 for j in range(item.floor)]
        if item.dormitory_id != 1:
            sec_first = floors
        ret['columnData'].append(floors)
    ret['columns'].append(fir_first)
    ret['columns'].append(sec_first)
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/get_dormitory', methods=['GET', 'POST'])
def get_dormitory():
    dormitory_id = request.json['dormitory_id']
    dormitory = Dormitory.query.get(dormitory_id)
    ret = {'dormitory': dormitory, 'floor': dormitory.floor}
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/get_where', methods=['GET', 'POST'])
def get_where():
    wx_id = request.json['wx_id']
    user = User.query.filter_by(wx_id=wx_id).first()
    dormitory = Dormitory.query.get(user.dormitory_id).dormitory
    ret = user.to_dict()
    ret['dormitory'] = dormitory
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/create_dormitory', methods=['GET', 'POST'])
def create_dormitory():
    if request.method == 'POST':
        dormitory = request.form['dormitory']
        floor = int(request.form['floor'])
        password = request.form['password']
        if password != app.config['password']:
            return '滚'
        pre_dormitory = Dormitory.query.filter_by(dormitory=dormitory).first()
        if pre_dormitory:
            return '宿舍已经存在'
        new_dormitory = Dormitory(dormitory=dormitory, floor=floor)
        db.session.add(new_dormitory)
        for i in range(floor):
            new_wc = Place(dormitory_id=Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
                           floor=i + 1,
                           occupied=0, place='wc')
            new_wa = Place(dormitory_id=Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
                           floor=i + 1,
                           occupied=0, place='wa')
            new_sh = Place(dormitory_id=Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
                           floor=i + 1,
                           occupied=0, place='sh')
            new_fan = Place(dormitory_id=Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
                            floor=i + 1,
                            occupied=0, place='fan')
            new_cai = Place(dormitory_id=Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
                            floor=i + 1,
                            occupied=0, place='cai')
            db.session.add(new_wc)
            db.session.add(new_wa)
            db.session.add(new_sh)
            db.session.add(new_fan)
            db.session.add(new_cai)
        db.session.commit()
        flash('新建成功')
    form = Build()
    return render_template('build.html', form=form)


@app.route('/go', methods=['GET', 'POST'])
def go():
    wx_id = request.json['wx_id']
    place = request.json['place']
    opt = request.json['opt']
    ret = {'status': 'Success'}
    if opt == 0:
        opt = -1
    user = User.query.filter_by(wx_id=wx_id).first()
    dormitory = Dormitory.query.get(user.dormitory_id).dormitory
    place = Place.query.filter(Place.place == place, Place.dormitory_id == user.dormitory_id,
                               Place.floor == user.floor).first()
    new_record = Record(user_id=user.user_id, place_id=place.place_id, opt=opt, student_number=user.student_number,
                        dormitory=dormitory, floor=user.floor, place=place.place, student_name=user.student_name,
                        room_number=user.room_number)
    Place.query.filter_by(place_id=place.place_id).update({'occupied': place.occupied + opt})
    User.query.filter_by(wx_id=wx_id).update({'status': user.status ^ (1 << app.config[place.place])})
    db.session.add(new_record)
    db.session.commit()
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/backend', methods=['GET', 'POST'])
def backend():
    ret = []
    if request.method == 'POST':
        dormitory = request.form['dormitory']
        floor = request.form['floor']
        place = request.form['place']
        opt = request.form['opt']
        student_number = request.form['student_number']
        records = Record.query.filter()
        if dormitory and dormitory != '未设定':
            records = records.filter_by(dormitory=dormitory)
        if floor:
            records = records.filter_by(floor=floor)
        if place:
            records = records.filter_by(place=place)
        if opt:
            records = records.filter_by(opt=opt)
        if student_number:
            records = records.filter_by(student_number=student_number)
        records = records.order_by(Record.created.desc()).all()
        flag = False
        wb = Workbook()
        ws = wb.active
        for record in records:
            record.place = cn_config[record.place]
            record.opt = cn_config[record.opt]
            temp = record.to_dict()
            ret.append(temp)
            if not flag:
                flag = True
                ws.append(list(cn_config[key] for key in temp.keys()))
            ws.append(list(temp.values()))
        pa = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'records.xlsx')
        ws.column_dimensions['D'].width = 20
        wb.save(pa)
    form = RecordForm()
    dormitories = Dormitory.query.all()
    form.dormitory.choices = [(it.dormitory, it.dormitory) for it in dormitories]
    return render_template('records.html', records=ret, form=form)


@app.route('/P2P/post', methods=['GET', 'POST'])
def post_p2p():
    wx_id = request.json['wx_id']
    description = request.json['description']
    user = User.query.filter_by(wx_id=wx_id).first()
    user_id = user.user_id
    dormitory_id = user.dormitory_id
    floor = user.floor
    new_post = P2P(user_id=user_id, dormitory_id=dormitory_id, floor=floor, description=description)
    db.session.add(new_post)
    db.session.commmit()


@app.route('/P2P', methods=['GET', 'POST'])
def p2p():
    wx_id = request.json['wx_id']
    scope = request.json['scope']
    buyer = User.query.filter_by(wx_id=wx_id).first()
    dormitory_id = buyer.dormitory_id
    floor = buyer.floor
    ret = []
    if scope == 'floor':
        items = P2P.query.filter(P2P.dormitory_id == dormitory_id, P2P.floor == floor).all()
        for item in items:
            dormitory = Dormitory.query.get(item.dormitory_id).first().dormitory
            floor = item.floor
            ret.append({'description': item.description, 'dormitory': dormitory, 'floor': floor})
    elif scope == 'dormitory':
        items = P2P.query.filter_by(dormitory_id=dormitory_id).all()
        for item in items:
            dormitory = Dormitory.query.get(item.dormitory_id).first().dormitory
            floor = item.floor
            ret.append({'description': item.description, 'dormitory': dormitory, 'floor': floor})
    else:
        items = P2P.query().all()
        for item in items:
            dormitory = Dormitory.query.get(item.dormitory_id).dormitory
            floor = item.floor
            ret.append({'description': item.description, 'dormitory': dormitory, 'floor': floor})
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/count', methods=['GET', 'POST'])
def count():
    dormitory_id = request.json['dormitory_id']
    floor = request.json['flor']
    place = request.json['place']
    ret = {'doing': Place.query.filter_by(dormitory_id=dormitory_id, floor=floor, place=place).first().occupied}
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/in_room', methods=['GET', 'POST'])
def in_room():
    wx_id = request.json['wx_id']
    place = request.json['place']
    user = User.query.filter_by(wx_id=wx_id).first()
    ret = {'rlt': False}
    if user.status & (1 << app.config[place]) != 0:
        ret['rlt'] = True
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/download/<file>', methods=['GET'])
def download(file):
    return send_from_directory('.', file, as_attachment=True)


@app.route('/ask_food', methods=['GET', 'POST'])
def ask_food():
    wx_id = request.json['wx_id']
    user = User.query.filter_by(wx_id=wx_id).first()
    dormitory = Dormitory.query.get(user.dormitory_id).dormitory
    fan_lib = Place.query.filter(Place.place == 'fan', Place.dormitory_id == user.dormitory_id,
                                 Place.floor == user.floor).first()
    cai_lib = Place.query.filter(Place.place == 'cai', Place.dormitory_id == user.dormitory_id,
                                 Place.floor == user.floor).first()
    fan_rec = Record.query.filter(Record.place == 'fan', Record.dormitory == dormitory,
                                  Record.floor == user.floor, Record.user_id == user.user_id).first()
    cai_rec = Record.query.filter(Record.place == 'cai', Record.dormitory == dormitory,
                                  Record.floor == user.floor, Record.user_id == user.user_id).first()
    fan_rest, cai_rest = fan_lib.occupied, cai_lib.occupied
    fan_had, cai_had = True, True
    if not fan_rec:
        fan_had = False
    if not cai_rec:
        cai_had = False
    ret = {'fan_rest': fan_rest, 'cai_rest': cai_rest, 'fan_had': fan_had, 'cai_had': cai_had}
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/get_food', methods=['GET', 'POST'])
def get_food():
    wx_id = request.json['wx_id']
    food = request.json['food']
    user = User.query.filter_by(wx_id=wx_id).first()
    dormitory = Dormitory.query.get(user.dormitory_id).dormitory
    food_lib = Place.query.filter(Place.place == food, Place.dormitory_id == user.dormitory_id,
                                  Place.floor == user.floor).first()
    food_rec = Record.query.filter(Record.place == food, Record.dormitory == dormitory,
                                   Record.floor == user.floor, Record.user_id == user.user_id).first()
    ret = {'status': 'Failed'}
    if not food_rec and food_lib.occupied:
        new_record = Record(user_id=user.user_id, place_id=food_lib.place_id, opt='1',
                            student_number=user.student_number, student_name=user.student_name, dormitory=dormitory,
                            floor=user.floor, place=food, room_number=user.room_number)
        ret['status'] = 'Success'
        Place.query.filter_by(place_id=food_lib.place_id).update({'occupied': food_lib.occupied - 1})
        db.session.add(new_record)
        db.session.commit()
    return Response(json.dumps(ret), mimetype='application/json')


@app.route('/add_food', methods=['GET', 'POST'])
def add_food():
    if request.method == 'POST':
        dormitory = request.form['dormitory']
        floor = request.form['floor']
        food = request.form['food']
        occupied = request.form['number']
        place = Place.query.filter(
            Place.dormitory_id == Dormitory.query.filter_by(dormitory=dormitory).first().dormitory_id,
            Place.floor == floor, Place.place == food)
        if not place.first():
            return '宿舍不存在'
        place.update({'occupied': place.first().occupied + int(occupied)})
        db.session.commit()
    form = AddFood()
    dormitories = Dormitory.query.all()
    form.dormitory.choices = [(it.dormitory, it.dormitory) for it in dormitories]
    return render_template('add_food.html', form=form)


@app.route('/food_clear', methods=['GET', 'POST'])
def food_clear():
    Place.query.filter(or_(Place.place == 'fan', Place.place == 'cai')).update({'occupied': 0})
    records = Record.query.filter(or_(Place.place == 'fan', Place.place == 'cai')).all()
    for record in records:
        db.session.delete(record)
    db.session.commit()
    return 'OK'


@app.route('/food_demand', methods=['GET', 'POST'])
def food_demand():
    ret = []
    if request.method == 'POST':
        dormitory = request.form['dormitory']
        floor = request.form['floor']
        records = Record.query.filter(or_(Record.place == 'fan', Record.place == 'cai'), Record.opt == '1')
        if dormitory and dormitory != '未设定':
            records = records.filter_by(dormitory=dormitory)
        if floor:
            records = records.filter_by(floor=floor)
        for record in records:
            record.place = cn_config[record.place]
            ret.append(record.to_dict())
    form = FoodDemand()
    dormitories = Dormitory.query.all()
    form.dormitory.choices = [(it.dormitory, it.dormitory) for it in dormitories]
    return render_template('food_demand.html', records=ret, form=form)


@app.route('/finish/<record_id>', methods=['GET', 'POST'])
def finish(record_id):
    record = Record.query.get(record_id)
    db.session.delete(record)
    db.session.commit()
    return redirect(url_for('food_demand'))


@app.route('/backend_', methods=['GET', 'POST'])
def backend_():
    dormitory_id = request.json['dormitory_id']
    place = request.json['place']
    student_name = request.json['student_name']
    floor = request.json['floor']
    ret = []
    records = Record.query.filter()
    if dormitory_id:
        records.filter_by(dormitory_id=dormitory_id)
    if place:
        records.filter_by(place=place)
    if student_name:
        records.filter_by(student_name=student_name)
    if floor:
        records.filter_by(floor=floor)
    records = records.order_by(Record.created.desc()).all()
    for record in records:
        record.place = cn_config[record.place]
        record.opt = cn_config[record.opt]
        temp = record.to_dict()
        ret.append(temp)
    return ret


if __name__ == '__main__':
    db.create_all()
    app.run(host='0.0.0.0', port=5000)
